<?php


namespace app\crm\model;

use app\admin\model\SysModel;
use app\mini\model\CustomerPointModel;
use think\facade\Db;
use app\admin\model\Common;

class ShopRetention extends Common
{

    /**
     * 为了数据库的整洁，同时又不影响Model和Controller的名称
     * 我们约定每个模块的数据表都加上相同的前缀，比如CRM模块用crm作为数据表前缀
     */
    protected $name = 'shop_retention';
    
    //数据 - 保有数据报表
    public function retentionList(array $request): array
    {
        $data = [];
        try {
            $where = [];
            // 等级搜索
            $level = isset($request['level']) && trim($request['level']) ? trim($request['level']) : '';
            $is_export_admin = isset($request['is_export_admin']) && trim($request['is_export_admin']) ? trim($request['is_export_admin']) : 0;
            if ($level)
            {
                $where[] = ['level', '=', $level];
            }
            // 查询昨日数据
            $today = date('Y-m-d', strtotime("-1 day"));
            $where[] = ['date', '=', $today];
            // 权限搜索
            if($is_export_admin == 1)
            {
                $request['admin_level'] = 1;
            }else
            {
                if(!empty($request['shop_id']))
                {
                    $where[] = ['shop_id', '=', $request['shop_id']];
                    $request['admin_level'] = 1;
                }elseif(!empty($request['group_name']))
                {
                    $where[] = ['group_name', '=', $request['group_name']];
                    $request['admin_level'] = 2;
                }elseif($request['admin_level'] < 3)
                {
                    $where[] = ['shop_id', 'in', $request['shopIds']];
                }
            }
            $dataCount = $this->where($where)->count();
            $data = ['list' => [], 'dataCount' => $dataCount];
            // 计算 30 天前的日期
            $now = date('Y-m-d');
            $thirtyDaysAgo = date('Y-m-d', strtotime('-31 days'));
            
            if ($dataCount)
            {
                $field = ['id', 'date', 'market_id', 'market_name', 'shop_id', 'shop_name', 'level', 'total_count', 'retained_count', 'lost_count', 'retention_rate', 'lost_rate', 'retained_ids', 'lost_ids', 'year_amount','md_amount','mz_amount','gongxian','group_name'];
                $list = $this->where($where)->field($field)->select()->toArray();
                
                $sumFields = ['total_count', 'retained_count', 'lost_count', 'year_amount', 'md_amount', 'mz_amount', 'lost_warning', 'reflux_count'];
                $total = array_fill_keys($sumFields, 0);
                $ids = ['allRetainedIds' => [], 'allLostIds' => [], 'allCustomerIds' => [], 'allLostWarningIds' => [], 'allRefluxIds' => []];
                // var_dump($list);die;
                foreach ($list as &$item)
                {
                    // 查询本月 流失预警总人
                    $lost_warning_ids_all = $this->whereBetween('date', [$thirtyDaysAgo, $now])->where('shop_id', $item['shop_id'])->where('level', $item['level'])->column('lost_warning_ids');
                    $all_lost_warning_ids = [];
                    if(!empty($lost_warning_ids_all) && is_array($lost_warning_ids_all))
                    {
                        foreach ($lost_warning_ids_all as $warning_ids)
                        {
                            $warning_ids = explode(',', $warning_ids);
                            foreach ($warning_ids as $wid)
                            {
                                if (!empty($wid))
                                {
                                    $all_lost_warning_ids[] = $wid;
                                }
                            }
                        }
                        $item['lost_warning_ids'] = array_unique($all_lost_warning_ids);
                        $item['lost_warning'] = count($item['lost_warning_ids']) ?? 0;
                        $item['lost_warning_ids'] = implode(',', $item['lost_warning_ids']) ?? "";
                    }else
                    {
                        $item['lost_warning_ids'] = "";
                        $item['lost_warning'] = 0;
                    }
                    // var_dump($item['lost_warning_ids'],$item['lost_warning']);
                    
                    // 查询本月 回流总人数
                    $reflux_ids_all = $this->whereBetween('date', [$thirtyDaysAgo, $now])->where('shop_id', $item['shop_id'])->where('level', $item['level'])->column('reflux_ids');
                    $all_reflux_ids = [];
                    if(!empty($reflux_ids_all) && is_array($reflux_ids_all))
                    {
                        foreach ($reflux_ids_all as $reflux_ids)
                        {
                            $reflux_ids = explode(',', $reflux_ids);
                            foreach ($reflux_ids as $rid)
                            {
                                if (!empty($rid))
                                {
                                    $all_reflux_ids[] = $rid;
                                }
                            }
                        }
                        $item['reflux_ids'] = array_unique($all_reflux_ids);
                        $item['reflux_count'] = count($item['reflux_ids']) ?? 0;
                        $item['reflux_ids'] = implode(',', $item['reflux_ids']) ?? "";
                    }else
                    {
                        $item['reflux_ids'] = "";
                        $item['reflux_count'] = 0;
                    }
                    
                    // 对比两个列表，如果流失预警ids的列表中包含回流ID，则在流失预警ID中踢出该ID
                    if (!empty($item['lost_warning_ids']) && !empty($item['reflux_ids']))
                    {
                        $lost_warning_id_array = explode(',', $item['lost_warning_ids']);
                        $reflux_id_array = explode(',', $item['reflux_ids']);
                    
                        // 过滤掉流失预警ID列表中包含的回流ID
                        $filtered_lost_warning_ids = array_diff($lost_warning_id_array, $reflux_id_array);
                    
                        $item['lost_warning_ids'] = implode(',', $filtered_lost_warning_ids);
                        $item['lost_warning'] = count($filtered_lost_warning_ids);
                    }
                    // 对比两个列表，如果保有ids中不包含此流失预警ID，则在流失预警ID中踢出该ID
                    if (!empty($item['lost_warning_ids']) && !empty($item['retained_ids']))
                    {
                        $lost_warning_id_array = explode(',', $item['lost_warning_ids']);
                        $retained_id_array = explode(',', $item['retained_ids']);
                    
                        // 过滤掉流失预警 ID 列表中不被包含在回流 ID 中的 ID
                        $filtered_lost_warning_ids = array_filter($lost_warning_id_array, function ($id) use ($retained_id_array)
                        {
                            return in_array($id, $retained_id_array);
                        });
                    
                        $item['lost_warning_ids'] = implode(',', $filtered_lost_warning_ids);
                        $item['lost_warning'] = count($filtered_lost_warning_ids);
                    }
                    
                    //合并流失ID + 回流ID
                    $item['customerIds'] = implode(',', array_filter([$item['retained_ids'], $item['lost_ids']]));
                    // 计算流失预警率
                    $item['lost_warning_rate'] = $item['lost_warning']? number_format(($item['reflux_count'] / $item['lost_warning']) * 100, 2).'%' :'0.00%';
                    // 保有
                    $item['all_baoyou'] = $this->where('shop_id', $item['shop_id'])->where('date',$today)->sum('retained_count');
                    foreach ($sumFields as $key)
                    {
                        $total[$key] += $item[$key];
                    }
                    foreach (['retained_ids', 'lost_ids', 'lost_warning_ids', 'reflux_ids'] as $idType)
                    {
                        if (!empty($item[$idType])) 
                        {
                            if (!isset($ids['all'.ucfirst($idType)]))
                            {
                                $ids['all'.ucfirst($idType)] = [];
                            }
                            $ids['all'.ucfirst($idType)] = array_merge($ids['all'.ucfirst($idType)], array_filter(explode(',', $item[$idType])));
                        }
                    }
                    if (!empty($item['customerIds']))
                    {
                        $ids['allCustomerIds'] = array_merge($ids['allCustomerIds'], explode(',', $item['customerIds']));
                    }
                }
                unset($item);
                //等级为3
                if ($request['admin_level'] == 3)
                {
                    $markets = [];
                    foreach ($list as $item)
                    {
                        $mid = $item['group_name'];
                        if (!isset($markets[$mid]))
                        {
                            $markets[$mid] = [
                                'market_id' => $item['market_id'],
                                // 'market_name' => $item['market_name'],
                                'market_name' => $item['group_name']."市场",
                                'group_name' => $item['group_name'],
                                'level' => $item['level'],
                                'total_count' => 0,
                                'retained_count' => 0,
                                'lost_count' => 0,
                                'year_amount' => 0,
                                'md_amount' => 0,
                                'mz_amount' => 0,
                                'retained_ids' => [],
                                'lost_ids' => [],
                                'customerIds' => [],
                                'lost_warning' => 0,
                                'lost_warning_ids' => [],
                                'reflux_count' => 0,
                                'reflux_ids' => [],
                            ];
                        }
                        foreach ($sumFields as $key)
                        {
                            $markets[$mid][$key] += $item[$key];
                        }
                        foreach (['retained_ids', 'lost_ids', 'customerIds', 'lost_warning_ids', 'reflux_ids'] as $idType)
                        {
                            $markets[$mid][$idType] = array_merge($markets[$mid][$idType], explode(',', $item[$idType]));
                        }
                    }
                    unset($item);
            
                    foreach ($markets as &$m)
                    {
                        $m['retention_rate'] = $m['total_count']? number_format(($m['retained_count'] / $m['total_count']) * 100, 2).'%' :'0.00%';
                        $m['lost_rate'] = $m['total_count']? number_format(($m['lost_count'] / $m['total_count']) * 100, 2) : 0;
                        $m['gongxian'] = $m['retained_count']? bcdiv(bcadd(bcdiv($m['mz_amount'], '2', 2), $m['md_amount'], 2), $m['retained_count'], 2) : 0;
                        $m['all_baoyou'] = $this->where('group_name', $m['group_name'])->where('date',$today)->sum('retained_count');
                        foreach (['year_amount','md_amount','mz_amount', 'gongxian'] as $key)
                        {
                            if (isset($m[$key])) {
                                $value = (float)$m[$key];
                                $m[$key] = number_format($value, 2);
                            }
                        }
            
                        // 计算流失预警率
                        $m['lost_warning_rate'] = $m['lost_warning']? number_format(($m['reflux_count'] / $m['lost_warning']) * 100, 2).'%' :'0.00%';
            
                        $m['retained_ids'] = implode(',', array_unique($m['retained_ids']));
                        $m['lost_ids'] = implode(',', array_unique($m['lost_ids']));
                        $m['customerIds'] = implode(',', array_unique($m['customerIds']));
                        $m['lost_warning_ids'] = implode(',', array_unique($m['lost_warning_ids']));
                        $m['reflux_ids'] = implode(',', array_unique($m['reflux_ids']));
                    }
                    unset($item);
                    $list = array_values($markets);
                }
                // 构建合计行
                $totalGongxian = $total['retained_count']? bcdiv(bcadd(bcdiv($total['mz_amount'], '2', 2), $total['md_amount'], 2), $total['retained_count'], 2) : 0;
                // 计算合计流失预警率
                $totalLostWarningRate = $total['lost_warning']? number_format(($total['reflux_count'] / $total['lost_warning']) * 100, 2).'%' :'0.00%';
                $totalRow = [
                    'market_name' => '合计',
                    'shop_name' => '合计',
                    'total_count' => $total['total_count'],
                    'retained_count' => $total['retained_count'],
                    'lost_count' => $total['lost_count'],
                    'retention_rate' => $total['total_count']? number_format(($total['retained_count'] / $total['total_count']) * 100, 2).'%' :'0.00%',
                    'lost_rate' => $total['total_count']? number_format(($total['lost_count'] / $total['total_count']) * 100, 2).'%' :'0.00%',
                    'retained_ids' => implode(',', array_unique($ids['allRetainedIds'])),
                    'lost_ids' => implode(',', array_unique($ids['allLostIds'])),
                    'customerIds' => implode(',', array_unique($ids['allCustomerIds'])),
                    'year_amount' => number_format($total['year_amount'], 2),
                    'md_amount' => number_format($total['md_amount'], 2),
                    'mz_amount' => number_format($total['mz_amount'], 2),
                    'gongxian' => $totalGongxian,
                    'lost_warning' => $total['lost_warning'],
                    'lost_warning_rate' => $totalLostWarningRate,
                    'lost_warning_ids' => implode(',', array_unique($ids['allLostWarningIds'])),
                    'reflux_count' => $total['reflux_count'],
                    'reflux_ids' => implode(',', array_unique($ids['allRefluxIds'])),
                ];
                if($request['admin_level'] == 1 || $request['admin_level'] == 2)
                {
                    if(!empty($request['shop_id']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('shop_id', $request['shop_id'])->sum('retained_count');
                    }elseif(!empty($request['group_name']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('group_name',$request['group_name'])->sum('retained_count');
                    }elseif(!empty($request['shopIds']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('shop_id','in', $request['shopIds'])->sum('retained_count');
                    }else
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->sum('retained_count');
                    }
                }else
                {
                    if(!empty($request['shop_id']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('shop_id', $request['shop_id'])->sum('retained_count');
                    }elseif(!empty($request['group_name']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('group_name',$request['group_name'])->sum('retained_count');
                    }elseif(!empty($request['shopIds']))
                    {
                        $totalRow['all_baoyou'] = $this->where('date',$today)->where('shop_id','in', $request['shopIds'])->sum('retained_count');
                    }else
                    {
                        $totalRow['all_baoyou'] = array_sum(array_column($list, 'all_baoyou'));
                    }
                }
                
                $data['list'] = array_merge($list, [$totalRow]);
            }
            
            if ($data['dataCount'] === 0 && $request['admin_level'] == 1)
            {
                $data['shop_name'] = Db::name('shop')->where('shop_id', 'in', $request['shopIds'])->value('shop_name');
            }
            return $data;
        } catch (\Exception $e) {
        }
        $this->error = '暂无此数据';
        return [];
    }
    
    
    
    
    
    //数据 - 保有数据报表 - ABCDE客户明细
    public function customerDetails(array $request): array
    {
        $warning = 0;
        $today = date('Y-m-d', strtotime("-1 day"));
        try{
            // 分页数据
            $is_export  = isset($request['is_export']) && intval($request['is_export']) ? intval($request['is_export']) : 0;
            $paging['page']  = isset($request['page']) && intval($request['page']) ? intval($request['page']) : 1;
            $paging['limit'] = isset($request['limit']) && intval($request['limit']) ? intval($request['limit']) : 15;
            
            $where = [];
            $baoliu = isset($request['baoliu']) && trim($request['baoliu']) ? trim($request['baoliu']) : '';
            
            if($baoliu == 'baoyou')// 保有
            {
                if(!empty($request['reflux_ids']))
                {
                    $customer_ids = isset($request['reflux_ids']) && trim($request['reflux_ids']) ? trim($request['reflux_ids']) : '';
                    $warning = 1;
                }else
                {
                    $customer_ids = isset($request['retained_ids']) && trim($request['retained_ids']) ? trim($request['retained_ids']) : '';
                }
            }elseif($baoliu == 'liushi')// 流失
            {
                if(!empty($request['lost_warning_ids']))
                {
                    $customer_ids = isset($request['lost_warning_ids']) && trim($request['lost_warning_ids']) ? trim($request['lost_warning_ids']) : '';
                    $warning = 1;
                }else
                {
                    $customer_ids = isset($request['lost_ids']) && trim($request['lost_ids']) ? trim($request['lost_ids']) : '';
                }
            }else //全部
            {
                $customer_ids = isset($request['customer_ids']) && trim($request['customer_ids']) ? trim($request['customer_ids']) : '';//全部ID
                $retained_ids = isset($request['retained_ids']) && trim($request['retained_ids']) ? trim($request['retained_ids']) : '';//保有ID
                $lost_ids = isset($request['lost_ids']) && trim($request['lost_ids']) ? trim($request['lost_ids']) : '';//流失ID
                
                // 将字符串形式的 ID 列表转换为数组
                $customer_id_array = explode(',', $customer_ids);
                $retained_id_array = explode(',', $retained_ids);
                $lost_id_array = explode(',', $lost_ids);
            }
            // var_dump($customer_ids);die;
            
            if(!empty($customer_ids))
            {
                if($is_export == 1)
                {
                    $list['data'] = Db::name('customer')->alias('c')
                        ->distinct(true)
                        ->field('c.id, c.shop_id, c.customer_name, c.telephone, c.sex, s.name as source_name, p.name as nurse_name, m.shop_name, c.last_kftime, c.cj_time')
                        ->join('setting s', 's.id = c.source_id', 'LEFT')
                        ->join('shop_person p', 'p.id = c.nurse_id', 'LEFT')
                        ->join('shop m', 'm.shop_id = c.shop_id', 'LEFT')
                        ->where('c.id', 'in', $customer_ids)
                        ->select()->toArray();
                }else
                {
                    $list = Db::name('customer')->alias('c')
                        ->distinct(true)
                        ->field('c.id, c.shop_id, c.customer_name, c.telephone, c.sex, s.name as source_name, p.name as nurse_name, m.shop_name, c.last_kftime, c.cj_time')
                        ->join('setting s', 's.id = c.source_id', 'LEFT')
                        ->join('shop_person p', 'p.id = c.nurse_id', 'LEFT')
                        ->join('shop m', 'm.shop_id = c.shop_id', 'LEFT')
                        ->where('c.id', 'in', $customer_ids)
                        ->paginate([
                            'list_rows' => $paging['limit'], 
                            'page' => $paging['page'],
                            'query' => request()->param()
                        ])->toArray();
                }
                    
                foreach($list['data'] as &$val)
                {
                    if($is_export == 1)
                    {
                        $val['sex'] = $val['sex'] === 1 ? '男' : ($val['sex'] === 2 ? '女' : '未知');
                    }
                    //查询是什么时间流失的
                    if($warning == 1)
                    {
                        $val['warning_time'] = $this->whereMonth('date')->where('lost_warning_ids','like','%'.$val['id'].'%')->order('id')->value('date');
                    }else
                    {
                        $val['warning_time'] = '';
                    }
                    // 年度消费金额 + 门店消费 + 门诊消费 + 门店贡献
                    $amount = $this->yearAmount($val['id'],$val['shop_id'],$today);//查询门店总金额
                    $val['year_amount'] = $amount['year_amount']?? 0;
                    $val['md_amount'] = $amount['md_amount']?? 0;
                    $val['mz_amount'] = $amount['mz_amount']?? 0;
                    //（(门诊消费/2)+门店消费）/客户数量
                    $val['gongxian'] = bcdiv(bcadd(bcdiv($amount['mz_amount'], '2', 2), $amount['md_amount'], 2), 1, 2);
                    
                    //预存余额查询
                    $balanceField = ['SUM(b.money) as money','SUM(b.complimentary_money) as complimentary_money','SUM(b.shop_transfer_money) as shop_transfer_money'];
                    $balanceList = Db::name('customer_balance')->alias('b')
                        ->join('customer_balance_category c', 'c.id = b.balance_category_id')
                        ->where('b.customer_id', '=', $val['id'])
                        ->field($balanceField)
                        ->group('b.balance_category_id')
                        ->select()
                        ->toArray();
                    $totalAllMoney = 0;
                    foreach ($balanceList as &$item)
                    {
                        $item['all_money'] = bcadd(bcadd($item['money'], $item['complimentary_money'], 2), $item['shop_transfer_money'], 2);
                        // 累加总预存金额
                        $totalAllMoney = bcadd($totalAllMoney, $item['all_money'], 2);
                    }
                    $val['yucun'] = $totalAllMoney;
                    
                     // 获取相同手机号的客户 ID
                    $related_ids = Db::name('customer')
                        ->where('telephone', $val['telephone'])
                        ->limit(3)
                        ->column('id');
                
                    // 最后核销时间
                    $last_hexiao_times = Db::name('service_log')
                        ->whereIn('customer_id', $related_ids)
                        ->where('is_cancel', '<>', 2)
                        ->order('create_time', 'desc')
                        ->column('create_time');
                        
                    $val['last_hexiao'] = !empty($last_hexiao_times) ? max($last_hexiao_times) : null;
                
                    // 最后消费时间
                    $shop_order_times = Db::name('shop_order')
                        ->whereIn('uid', $related_ids)
                        ->where('status', 2)
                        ->where('is_pay', 1)
                        ->where('total_amount', '>', 0)
                        ->order('deal_time', 'desc')
                        ->column('deal_time');
                
                    $refund_order_times = Db::name('shop_refund_order')
                        ->whereIn('uid', $related_ids)
                        ->where('status', 2)
                        ->where('type', 3)
                        ->where('refund_type', '101.02')
                        ->order('deal_time', 'desc')
                        ->column('deal_time');
                
                    $all_consumption_times = array_merge($shop_order_times, $refund_order_times);
                    $val['last_xiaofei'] = !empty($all_consumption_times) ? max($all_consumption_times) : null;
                    
                    //判断用户是保有还是流失
                    if($baoliu == 'baoyou')
                    {
                        $val['baoliu'] = '保有';
                    }elseif($baoliu == 'liushi')
                    {
                        $val['baoliu'] = '流失';
                    }else
                    {
                        if (in_array($val['id'], $retained_id_array))
                        {
                            $val['baoliu'] = '保有';
                        } elseif (in_array($val['id'], $lost_id_array))
                        {
                            $val['baoliu'] = '流失';
                        }
                    }
                    $val['cj_time'] = ($val['cj_time'] != 0) ? (date('Y-m-d', $val['cj_time'])?? '') : "";
                    $val['last_kftime'] = ($val['last_kftime'] != 0) ? (date('Y-m-d', $val['last_kftime'])?? '') : "";
                    $val['last_xiaofei'] = ($val['last_xiaofei'] != 0) ? (date('Y-m-d', $val['last_xiaofei'])?? '') : "";
                    $val['last_hexiao'] = ($val['last_hexiao'] != 0) ? (date('Y-m-d', $val['last_hexiao'])?? '') : "";
                }
                    // 重新排序
                if($warning == 1)
                {
                    usort($list['data'], function ($a, $b) {
                        return strtotime($a['warning_time']) - strtotime($b['warning_time']);
                    });
                }else
                {
                    usort($list['data'], function ($a, $b)
                    {
                        if ($a['baoliu'] === '保有' && $b['baoliu'] === '流失')
                        {
                            return -1; // $a 应该排在 $b 前面
                        } elseif ($a['baoliu'] === '流失' && $b['baoliu'] === '保有')
                        {
                            return 1;  // $a 应该排在 $b 后面
                        }else
                        {
                            return 0;  // 保持原有顺序
                        }
                    });
                }
            }
            $data['list']      = $list ?? [];
            return $data;
        } catch (\Exception $e){
        }
        $this->error = '暂无此数据';
        return [];
    }
    
    
    
    
    
    //年度保有消费
    public function yearAmount($customerIds,$shop_id,$today)
    {
        $res['md_amount'] = 0;
        $res['mz_amount'] = 0;
        $res['year_amount'] = 0;
        $start_time =  strtotime($today.' -365 day');
        $end_time = strtotime($today);
        
        $telArray = Db::name('customer')->where('id',$customerIds)->column('telephone');
        //查询门诊用户
        $menzhenArray =  Db::name('customer')->alias('c')
            ->join('shop s', 'c.shop_id=s.shop_id', 'LEFT')
            ->where('c.customer_name', 'notlike', '%美团%')
            ->where('c.status', 1)
            ->where('c.telephone', 'in', $telArray)
            ->where('s.shop_type', 1)    
            ->column('c.id');
        if (!is_array($customerIds))
        {
            $customerIds = explode(',', (string)$customerIds);
        }
        $customerIdsArray = array_merge($customerIds,$menzhenArray);
        
        
        
        // 初始化 $md 和 $mz 列表
        $md = [];
        $mz = [];
        // 根据 shop_id 进行分类
        foreach ($customerIdsArray as $id)
        {
            // 查询该客户 ID 对应的 shop_id
            $shopIdOfRelated = Db::name('customer')->where('id', $id)->value('shop_id');
            if ($shopIdOfRelated == $shop_id)
            {
                $md[] = $id;
            } else {
                $mz[] = $id;
            }
        }
        
        // 计算门店收款和退款总和
        $md_sum = Db::name('shop_refund_order')
            ->where([
                ['status', '=', 2], // 已下推
                ['uid', 'in', $md],
                ['deal_time', 'between', [$start_time, $end_time]]
            ])
            ->field([
                // 计算收款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 3 AND `refund_type` = '101.02' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_shoukuan",
                // 计算退款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 1 AND `refund_type` <> '101.03' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_tuikuan"
            ])
            ->find();
        
        // 使用 bcsub 进行高精度减法运算
        $res['md_amount'] = bcsub($md_sum['total_shoukuan'], $md_sum['total_tuikuan'], 2); // 保留 2 位小数
        
        // 计算门诊收款和退款总和
        $mz_sum = Db::name('shop_refund_order')
            ->where([
                ['status', '=', 2], // 已下推
                ['uid', 'in', $mz],
                ['deal_time', 'between', [$start_time, $end_time]]
            ])
            ->field([
                // 计算收款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 3 AND `refund_type` = '101.02' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_shoukuan",
                // 计算退款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 1 AND `refund_type` <> '101.03' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_tuikuan"
            ])
            ->find();
        
        // 使用 bcsub 进行高精度减法运算
        $res['mz_amount'] = bcsub($mz_sum['total_shoukuan'], $mz_sum['total_tuikuan'], 2); // 保留 2 位小数
        
        // 使用 bcadd 进行高精度加法运算
        $res['year_amount'] = bcadd($res['md_amount'], $res['mz_amount'], 2); // 保留 2 位小数
        // 输出结果
        return $res;
        
    }
    
    
    
    
}